package controller;

/**
 *
 * @author Sujen
 */
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import model.Lid;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import persistence.SessionManager;
import security.ActivationKey;

public class RapportageController extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        DateFormat dateFormat = new SimpleDateFormat("MM");
        Date today = new Date();
        String todayString = dateFormat.format(today);
        NumberFormat fo = new DecimalFormat("#0.0");
        Session session;
        session = SessionManager.openSession();
        Transaction tx = session.beginTransaction();

        String jaar = "2012";
        String kwartaal = "03";

        NumberFormat kwartaals = new DecimalFormat("#00");
        Double dou = Double.parseDouble(kwartaal);
        Double dou2 = dou - 1;
        String kwartaal2 = kwartaals.format(dou2).toString();

        Double leeftijd = (Double) session.createQuery("select avg(year(l.lidGeboortedatum)) from Lid as l").uniqueResult();
        Number totalLid = (Number) session.createQuery("select count(*) from Lid").uniqueResult();
        Number todayLid = (Number) session.createQuery("select count(*) from Lid as l where l.lidRegistered >= '2012-01-01 00:00:00' and l.lidRegistered < '2012-12-31 00:00:00'").uniqueResult();
        List provincie = (List) session.createQuery("select rit.ritVanProvincie from Rit as rit group by rit.ritVanProvincie").list();
//            List provincieRit = (List) session.createQuery("select count(*) from Rit as rit group by rit.ritVanProvincie").list();
        Number provincieLid1 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Groningen'").uniqueResult();
        Number provincieLid2 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Limburg'").uniqueResult();
        Number provincieLid3 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Noord-Brabant'").uniqueResult();
        Number provincieLid4 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Zeeland'").uniqueResult();
        Number provincieLid5 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Zuid-Holland'").uniqueResult();
        Number provincieLid6 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Noord-Holland'").uniqueResult();
        Number provincieLid7 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Utrecht'").uniqueResult();
        Number provincieLid8 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Gelderland'").uniqueResult();
        Number provincieLid9 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Flevoland'").uniqueResult();
        Number provincieLid10 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Overijssel'").uniqueResult();
        Number provincieLid11 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Friesland'").uniqueResult();
        Number provincieLid12 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Drenthe'").uniqueResult();
        Double leeftijdLid = 2012 - leeftijd;


        request.setAttribute("provincie", provincie);
//            request.setAttribute("provincieRit", provincieRit);
        request.setAttribute("leeftijd", fo.format(leeftijdLid));
        request.setAttribute("total", totalLid);
        request.setAttribute("today", todayLid);
        request.setAttribute("jaar", jaar);
        request.setAttribute("kwartaal", kwartaal);
        request.setAttribute("Groningen", provincieLid1);
        request.setAttribute("Limburg", provincieLid2);
        request.setAttribute("No", provincieLid3);
        request.setAttribute("Zeeland", provincieLid4);
        request.setAttribute("Zuid", provincieLid5);
        request.setAttribute("Noord", provincieLid6);
        request.setAttribute("Utrecht", provincieLid7);
        request.setAttribute("Gelderland", provincieLid8);
        request.setAttribute("Flevoland", provincieLid9);
        request.setAttribute("Overijssel", provincieLid10);
        request.setAttribute("Friesland", provincieLid11);
        request.setAttribute("Drenthe", provincieLid12);


        String address = "/rapportage.jsp";
        RequestDispatcher dispatcher = request.getRequestDispatcher(address);
        dispatcher.forward(request, response);
    }

    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String kwartaal = request.getParameter("kwartaal");
        String jaar = request.getParameter("jaar");

        String kwartaal2 = new String();
        String kwartaal3 = new String();



        if (kwartaal.equals("") && !jaar.equals("")) {
        DateFormat dateFormat = new SimpleDateFormat("MM");
        Date today = new Date();
        String todayString = dateFormat.format(today);
            NumberFormat fo = new DecimalFormat("#0.0");
            Session session;
            session = SessionManager.openSession();
            Transaction tx = session.beginTransaction();

            Double leeftijd = (Double) session.createQuery("select avg(year(l.lidGeboortedatum)) from Lid as l where l.lidRegistered > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (leeftijd == null) {
                leeftijd = 0.0;
            }
            Number totalLid = (Number) session.createQuery("select count(*) from Lid").uniqueResult();
            if (totalLid == null) {
                totalLid = 0;
            }
            Number todayLid = (Number) session.createQuery("select count(*) from Lid as l where l.lidRegistered > '" + jaar + "-01-01 00:00:00' and l.lidRegistered < '" + jaar + "-12-31 00:00:00'").uniqueResult();
            if (todayLid == null) {
                todayLid = 0;
            }
            List provincie = (List) session.createQuery("select rit.ritVanProvincie from Rit as rit group by rit.ritVanProvincie where rit.ritDatum > '" + jaar + "-01-01 00:00:00'").list();
//            List provincieRit = (List) session.createQuery("select count(*) from Rit as rit group by rit.ritVanProvincie").list();
            Number provincieLid1 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Groningen' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid1 == null) {
                provincieLid1 = 0;
            }
            Number provincieLid2 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Limburg' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid2 == null) {
                provincieLid2 = 0;
            }
            Number provincieLid3 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Noord-Brabant' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid3 == null) {
                provincieLid3 = 0;
            }
            Number provincieLid4 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Zeeland' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid4 == null) {
                provincieLid4 = 0;
            }
            Number provincieLid5 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Zuid-Holland' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid5 == null) {
                provincieLid5 = 0;
            }
            Number provincieLid6 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Noord-Holland' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid6 == null) {
                provincieLid6 = 0;
            }
            Number provincieLid7 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Utrecht' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid7 == null) {
                provincieLid7 = 0;
            }
            Number provincieLid8 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Gelderland' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid8 == null) {
                provincieLid8 = 0;
            }
            Number provincieLid9 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Flevoland' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid9 == null) {
                provincieLid9 = 0;
            }
            Number provincieLid10 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Overijssel' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid10 == null) {
                provincieLid10 = 0;
            }
            Number provincieLid11 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Friesland' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid11 == null) {
                provincieLid11 = 0;
            }
            Number provincieLid12 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Drenthe' and rit.ritDatum > '" + jaar + "-01-01 00:00:00'").uniqueResult();
            if (provincieLid12 == null) {
                provincieLid12 = 0;
            }
            Double leeftijdLid = 2012 - leeftijd;
            if (leeftijdLid > 1000) {
                leeftijdLid = 0.0;
            }

            request.setAttribute("provincie", provincie);
//            request.setAttribute("provincieRit", provincieRit);
            request.setAttribute("leeftijd", fo.format(leeftijdLid));
            request.setAttribute("total", totalLid);
            request.setAttribute("today", todayLid);
            request.setAttribute("Groningen", provincieLid1);
            request.setAttribute("jaar", jaar);
            request.setAttribute("Limburg", provincieLid2);
            request.setAttribute("No", provincieLid3);
            request.setAttribute("Zeeland", provincieLid4);
            request.setAttribute("Zuid", provincieLid5);
            request.setAttribute("Noord", provincieLid6);
            request.setAttribute("Utrecht", provincieLid7);
            request.setAttribute("Gelderland", provincieLid8);
            request.setAttribute("Flevoland", provincieLid9);
            request.setAttribute("Overijssel", provincieLid10);
            request.setAttribute("Friesland", provincieLid11);
            request.setAttribute("Drenthe", provincieLid12);

        } else if (!kwartaal.equals("") && !jaar.equals("")) {

            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date today = new Date();
            String todayString = dateFormat.format(today);
            NumberFormat fo = new DecimalFormat("#0.0");
            NumberFormat kwartaals = new DecimalFormat("#00");
            Session session;
            session = SessionManager.openSession();
            Transaction tx = session.beginTransaction();
            Double dou = Double.parseDouble(kwartaal);
            Integer dao = Integer.parseInt(kwartaal);
            Integer kwart = (Integer) (dao / 3);
            String kwart2 = kwart.toString();
            Double dou2 = dou - 3;
            Double dou3 = dou - 1;
            kwartaal2 = kwartaals.format(dou2).toString();
            kwartaal3 = kwartaals.format(dou3).toString();

            Double leeftijd = (Double) session.createQuery("select avg(year(l.lidGeboortedatum)) from Lid as l where l.lidRegistered < '" + jaar + "-" + kwartaal + "-01 00:00:00' and l.lidRegistered > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (leeftijd == null) {
                leeftijd = 0.0;
            }
            Number monthLid = (Number) session.createQuery("select count(*) from Lid as l where l.lidRegistered < '" + jaar + "-" + kwartaal + "-01 00:00:00' and l.lidRegistered > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (monthLid == null) {
                monthLid = 0;
            }
            Number totalLid = (Number) session.createQuery("select count(*) from Lid as l where l.lidRegistered < '" + jaar + "-" + kwartaal + "-01 00:00:00'").uniqueResult();
            if (totalLid == null) {
                totalLid = 0;
            }
            List provincie = (List) session.createQuery("select rit.ritVanProvincie from Rit as rit group by rit.ritVanProvincie where rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").list();
//            List provincieRit = (List) session.createQuery("select count(*) from Rit as rit group by rit.ritVanProvincie").list();
            Number provincieLid1 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Groningen' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid1 == null) {
                provincieLid1 = 0;
            }
            Number provincieLid2 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Limburg' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid2 == null) {
                provincieLid2 = 0;
            }
            Number provincieLid3 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Noord-Brabant' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid3 == null) {
                provincieLid3 = 0;
            }
            Number provincieLid4 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Zeeland' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid4 == null) {
                provincieLid4 = 0;
            }
            Number provincieLid5 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Zuid-Holland' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid5 == null) {
                provincieLid5 = 0;
            }
            Number provincieLid6 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Noord-Holland' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid6 == null) {
                provincieLid6 = 0;
            }
            Number provincieLid7 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Utrecht' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid7 == null) {
                provincieLid7 = 0;
            }
            Number provincieLid8 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Gelderland' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid8 == null) {
                provincieLid8 = 0;
            }
            Number provincieLid9 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Flevoland' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid9 == null) {
                provincieLid9 = 0;
            }
            Number provincieLid10 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Overijssel' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid10 == null) {
                provincieLid10 = 0;
            }
            Number provincieLid11 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Friesland' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid11 == null) {
                provincieLid11 = 0;
            }
            Number provincieLid12 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Drenthe' and rit.ritDatum < '" + jaar + "-" + kwartaal + "-01 00:00:00' and rit.ritDatum > '" + jaar + "-" + kwartaal2 + "-01 00:00:00'").uniqueResult();
            if (provincieLid12 == null) {
                provincieLid12 = 0;
            }
            Double leeftijdLid = 2012 - leeftijd;
            if (leeftijdLid > 1000) {
                leeftijdLid = 0.0;
            }

            request.setAttribute("provincie", provincie);
//            request.setAttribute("provincieRit", provincieRit);
            request.setAttribute("leeftijd", fo.format(leeftijdLid));
            request.setAttribute("total", totalLid);
            request.setAttribute("today", monthLid);
            request.setAttribute("Groningen", provincieLid1);
            request.setAttribute("jaar", jaar);
            request.setAttribute("kwartaal", kwartaal);
            request.setAttribute("quarter", kwart2);
            request.setAttribute("Limburg", provincieLid2);
            request.setAttribute("No", provincieLid3);
            request.setAttribute("Zeeland", provincieLid4);
            request.setAttribute("Zuid", provincieLid5);
            request.setAttribute("Noord", provincieLid6);
            request.setAttribute("Utrecht", provincieLid7);
            request.setAttribute("Gelderland", provincieLid8);
            request.setAttribute("Flevoland", provincieLid9);
            request.setAttribute("Overijssel", provincieLid10);
            request.setAttribute("Friesland", provincieLid11);
            request.setAttribute("Drenthe", provincieLid12);
        } else {
                    DateFormat dateFormat = new SimpleDateFormat("MM");
        Date today = new Date();
        String todayString = dateFormat.format(today);
            NumberFormat fo = new DecimalFormat("#0.0");
            Session session;
            session = SessionManager.openSession();
            Transaction tx = session.beginTransaction();

            Double leeftijd = (Double) session.createQuery("select avg(year(l.lidGeboortedatum)) from Lid as l").uniqueResult();
            Number totalLid = (Number) session.createQuery("select count(*) from Lid").uniqueResult();
            Number todayLid = (Number) session.createQuery("select count(*) from Lid as l where l.lidRegistered > '2012-01-01 00:00:00' and l.lidRegistered < '2012-12-31 00:00:00'").uniqueResult();
            List provincie = (List) session.createQuery("select rit.ritVanProvincie from Rit as rit group by rit.ritVanProvincie").list();
//            List provincieRit = (List) session.createQuery("select count(*) from Rit as rit group by rit.ritVanProvincie").list();
            Number provincieLid1 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Groningen'").uniqueResult();
            Number provincieLid2 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Limburg'").uniqueResult();
            Number provincieLid3 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Noord-Brabant'").uniqueResult();
            Number provincieLid4 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Zeeland'").uniqueResult();
            Number provincieLid5 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Zuid-Holland'").uniqueResult();
            Number provincieLid6 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Noord-Holland'").uniqueResult();
            Number provincieLid7 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Utrecht'").uniqueResult();
            Number provincieLid8 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Gelderland'").uniqueResult();
            Number provincieLid9 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Flevoland'").uniqueResult();
            Number provincieLid10 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Overijssel'").uniqueResult();
            Number provincieLid11 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Friesland'").uniqueResult();
            Number provincieLid12 = (Number) session.createQuery("select count(*) from Rit as rit where rit.ritVanProvincie = 'Drenthe'").uniqueResult();
            Double leeftijdLid = 2012 - leeftijd;


            request.setAttribute("provincie", provincie);
//            request.setAttribute("provincieRit", provincieRit);
            request.setAttribute("leeftijd", fo.format(leeftijdLid));
            request.setAttribute("total", totalLid);
            request.setAttribute("today", todayLid);
            request.setAttribute("jaar", jaar);
            request.setAttribute("kwartaal", kwartaal);
            request.setAttribute("Groningen", provincieLid1);
            request.setAttribute("Limburg", provincieLid2);
            request.setAttribute("No", provincieLid3);
            request.setAttribute("Zeeland", provincieLid4);
            request.setAttribute("Zuid", provincieLid5);
            request.setAttribute("Noord", provincieLid6);
            request.setAttribute("Utrecht", provincieLid7);
            request.setAttribute("Gelderland", provincieLid8);
            request.setAttribute("Flevoland", provincieLid9);
            request.setAttribute("Overijssel", provincieLid10);
            request.setAttribute("Friesland", provincieLid11);
            request.setAttribute("Drenthe", provincieLid12);

        }

        String address = "/rapportage.jsp";
        RequestDispatcher dispatcher = request.getRequestDispatcher(address);
        dispatcher.forward(request, response);
    }
}
